const connection = require("../app/database")

class ItemService {
    async create(name,number,volume,categoryId,stashId) {
        const statement = `INSERT INTO item (name,number,volume,category_id,stash_id) VALUES (?,?,?,?,?);`
        const [result] = await connection.execute(statement,[name,number,volume,categoryId,stashId])
        return result
    }
    async getListCount() {
        const statement = `SELECT COUNT(*) AS totalCount from item;`
        const [result] = await connection.execute(statement)
        return result[0]
    }
    async getList(pageSize,offset) {     
        const statement = `
            SELECT item.*, stash.name stashName
            FROM item
            INNER JOIN stash ON item.stash_id = stash.id
            ORDER BY item.id DESC limit ${pageSize} offset ${offset};
        `;
        const [result] = await connection.execute(statement);
        return result
    }
    async getListByStashIdAndCategoryId(stashId,categoryId) {
        const statement = `SELECT * from item WHERE stash_id = ? AND category_id = ?;`
        const [result] = await connection.execute(statement,[stashId,categoryId])
        return result
    }
    async getLast7DataList() {
        const statement =  `SELECT * 
        FROM item 
        WHERE createTime >= CURDATE() - INTERVAL 7 DAY;`
        const [result] = await connection.execute(statement)
        return result
    }
    async getCategoryEnterAndOut() {
        const statement = `
                            SELECT a.name stash_name,c.name category_name,b.number
                            from item b
                            left join category c
                                on b.category_id= c.id
                            left join  stash a
                                on a.id = b.stash_id;
                        `
        const [result] = await connection.execute(statement)
        return result
    }
}

module.exports = new ItemService()